﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class RunSQLObject
    {
        //Проверка версии БД при старте программы
        public const string sql_CheckingTableVersionDB = @"select (case when count(*) = 0 then 0 when count(*) > 0 then 1 end) as CheckingTableVersionDB from INFORMATION_SCHEMA.tables as tables where table_name = 'VersionDB'";
        public int CheckingTableVersionDB { get; set; }
        public const string sql_MaxVersionDB = @"SELECT MAX([Version]) as MaxVersionDB FROM [VersionDB]";
        public int MaxVersionDB { get; set; }
        //Получения настроек для конструкторов
        public const string sql_WorkFromDate = @"SELECT TOP(1)convert(datetime, Settings.Value, 104) as WorkFromDate FROM Settings WHERE Code = 'rep_work_constr_from_date'";
        public DateTime WorkFromDate { get; set; }
        public const string sql_WorkToDate = @"SELECT TOP(1)convert(datetime, Settings.Value, 104) as WorkToDate FROM Settings WHERE Code = 'rep_work_constr_to_date'";
        public DateTime WorkToDate { get; set; }
        public const string sql_FinFromDate = @"SELECT TOP(1)convert(datetime, Settings.Value, 104) as FinFromDate FROM Settings WHERE Code = 'rep_fin_constr_from_date'";
        public DateTime FinFromDate { get; set; }
        public const string sql_FinToDate = @"SELECT TOP(1)convert(datetime, Settings.Value, 104) as FinToDate FROM Settings WHERE Code = 'rep_fin_constr_to_date'";
        public DateTime FinToDate { get; set; }
        //Вставка новых 4-х настроек для конструктора
        public const string sql_insert_set1 = @"INSERT INTO [Settings] ([Code],[Value],[Discription]) VALUES (N'rep_work_constr_from_date',N'01.01.2013',N'Дата с. Настройки конструктора Работы')";
        public const string sql_insert_set2 = @"INSERT INTO [Settings] ([Code],[Value],[Discription]) VALUES (N'rep_work_constr_to_date',N'31.12.2020',N'Дата по. Настройки конструктора Работы')";
        public const string sql_insert_set3 = @"INSERT INTO [Settings] ([Code],[Value],[Discription]) VALUES (N'rep_fin_constr_from_date',N'01.01.2013',N'Дата с. Настройки конструктора Финансы')";
        public const string sql_insert_set4 = @"INSERT INTO [Settings] ([Code],[Value],[Discription]) VALUES (N'rep_fin_constr_to_date',N'31.12.2020',N'Дата по. Настройки конструктора Финансы')";
        //Обновление версии БД (версия 10)
        public const string sql_InsertVersion10 = @"INSERT INTO [VersionDB] ([Version], [Data]) VALUES (10,GetDate());"; 
        
        //Обновление БД до версии 20:
        public const string sql_update_db_for_v20_23 = @"ALTER TABLE [Work] ADD [OperType] int Default 1 NOT NULL;";
        public const string sql_update_db_for_v20_24 = @"ALTER TABLE [Work] ADD CONSTRAINT [FK_OperType] FOREIGN KEY ([OperType]) REFERENCES [DictionaryOperTypes]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v20_1 = @"ALTER TABLE [Work] ADD [Distance] float NULL;";

        public const string sql_update_db_for_v20_5 = @"ALTER TABLE [Work] ALTER COLUMN [Customer] int NULL;";
        public const string sql_update_db_for_v20_6 = @"ALTER TABLE [Work] ALTER COLUMN [PriceOfSales] money NULL;";
        public const string sql_update_db_for_v20_7 = @"ALTER TABLE [Work] ALTER COLUMN [CurrencySales] int NULL;";
        public const string sql_update_db_for_v20_25 = @"ALTER TABLE [Work] ALTER COLUMN [Machine] int NULL;";
        public const string sql_update_db_for_v20_26 = @"ALTER TABLE [Work] ALTER COLUMN [Driver] int NULL;";
        public const string sql_update_db_for_v20_27 = @"ALTER TABLE [Work] ALTER COLUMN [SalaryDriver] money NULL;";
        public const string sql_update_db_for_v20_28 = @"ALTER TABLE [Work] ALTER COLUMN [Goods] int NULL;";
        public const string sql_update_db_for_v20_29 = @"ALTER TABLE [Work] ALTER COLUMN [Weight] float NULL;";
        public const string sql_update_db_for_v20_30 = @"UPDATE [Work] SET [OperType] = 1;";
        public const string sql_update_db_for_v20_31 = @"UPDATE [Work] SET [OperType] = 9 where Discription like '%Перенос остатков%';";
        public const string sql_update_db_for_v20_2 = @"ALTER TABLE [DictionaryPlaces] ADD [Store] int NOT NULL DEFAULT 0;";
        public const string sql_update_db_for_v20_3 = @"ALTER TABLE [Transactions] ADD [Liters] float NULL;";
        public const string sql_update_db_for_v20_4 = @"ALTER TABLE [DictionaryCategories] ADD [Fuel] int NOT NULL DEFAULT 0;";

        public const string sql_update_db_for_v20_8 = @"CREATE TABLE [DictionaryGasStations] ([Id] int IDENTITY (1,1) NOT NULL, [Name] nvarchar(100) NOT NULL, [View] int NOT NULL);";
        public const string sql_update_db_for_v20_9 = @"ALTER TABLE [DictionaryGasStations] ADD CONSTRAINT [PK_DictionaryGasStations] PRIMARY KEY ([Id]);";
        public const string sql_update_db_for_v20_10 = @"
        CREATE TABLE [Refueling] (
          [Id] bigint IDENTITY (1,1) NOT NULL
        , [Date] datetime NOT NULL
        , [GasStation] int NOT NULL
        , [Machine] int NOT NULL
        , [Fuel] int NOT NULL
        , [Liters] float NOT NULL
        , [Project] int NOT NULL
        , [Discription] nvarchar(255) NULL);";
        public const string sql_update_db_for_v20_11 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [PK_Refueling] PRIMARY KEY ([Id]);";
        public const string sql_update_db_for_v20_12 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_GasStations] FOREIGN KEY ([GasStation]) REFERENCES [DictionaryGasStations]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v20_13 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_Machine] FOREIGN KEY ([Machine]) REFERENCES [DictionaryMachines]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v20_14 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_ProjectRefueling] FOREIGN KEY ([Project]) REFERENCES [DictionaryProject]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v20_15 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_Fuel] FOREIGN KEY ([Fuel]) REFERENCES [DictionaryCategories]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v20_16 = @"CREATE TABLE [DictionaryOperTypes] ([Id] int NOT NULL, [Name] nvarchar(100) NOT NULL);";
        public const string sql_update_db_for_v20_17 = @"ALTER TABLE [DictionaryOperTypes] ADD CONSTRAINT [PK_DictionaryOperTypes] PRIMARY KEY ([Id]);";

        public const string sql_update_db_for_v20_18 = @"INSERT INTO [DictionaryOperTypes] ([Id],[Name]) VALUES (1,N'Продажа+Доставка');";
        public const string sql_update_db_for_v20_19 = @"INSERT INTO [DictionaryOperTypes] ([Id],[Name]) VALUES (2,N'Перевозка');";
        public const string sql_update_db_for_v20_20 = @"INSERT INTO [DictionaryOperTypes] ([Id],[Name]) VALUES (3,N'Продажа');";
        public const string sql_update_db_for_v20_21 = @"INSERT INTO [DictionaryOperTypes] ([Id],[Name]) VALUES (4,N'Услуга');";
        public const string sql_update_db_for_v20_22 = @"INSERT INTO [DictionaryOperTypes] ([Id],[Name]) VALUES (9,N'Перенос остатков');";

        public const string sql_update_db_for_v20_32 = @"INSERT INTO [VersionDB] ([Version], [Data]) VALUES (20,GetDate());";

        //Обновление БД до версии 30:
        
        public const string sql_update_db_for_v30_1 = @"ALTER TABLE [DictionaryPartners] ADD [FuelSupplier] int NOT NULL DEFAULT 0;";
        public const string sql_update_db_for_v30_2 = @"
        CREATE TABLE [DictionaryFuelOperTypes] (
          [Id] int NOT NULL
        , [Name] nvarchar(100) NOT NULL
        );";

        public const string sql_update_db_for_v30_3 = @"ALTER TABLE [DictionaryFuelOperTypes] ADD CONSTRAINT [PK_DictionaryFuelOperTypes] PRIMARY KEY ([Id]);";

        public const string sql_update_db_for_v30_4 = @"INSERT INTO [DictionaryFuelOperTypes] ([Id],[Name]) VALUES (1,N'Своя АЗС');";
        public const string sql_update_db_for_v30_5 = @"INSERT INTO [DictionaryFuelOperTypes] ([Id],[Name]) VALUES (2,N'Поставщик');";


        public const string sql_update_db_for_v30_6 = @"     
        CREATE TABLE [DictionaryFuelType] (
          [Id] int IDENTITY (1,1) NOT NULL
        , [Name] nvarchar(100) NOT NULL
        , [View] int NOT NULL
        );";
        public const string sql_update_db_for_v30_7 = @"ALTER TABLE [DictionaryFuelType] ADD CONSTRAINT [PK_DictionaryFuelType] PRIMARY KEY ([Id]);";


        public const string sql_update_db_for_v30_8 = @"DROP TABLE [Refueling];";
        public const string sql_update_db_for_v30_9 = @"
        CREATE TABLE [Refueling] (
          [Id] bigint IDENTITY (1,1) NOT NULL
        , [Date] datetime NOT NULL
        , [FuelOperType] int NOT NULL
        , [FuelSupplier] int NULL
        , [FuelType] int NULL
        , [Machine] int NOT NULL
        , [Driver] int NOT NULL
        , [Liters] float NOT NULL
        , [Price] money NULL
        , [FuelRemaining] float NULL
        , [Distance] float NULL
        , [Odometer] float NULL
        , [Fuel] int NULL
        , [GasStation] int NULL
        , [Project] int NOT NULL
        , [Discription] nvarchar(255) NULL
        );";
        public const string sql_update_db_for_v30_10 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [PK_Refueling] PRIMARY KEY ([Id]);";
        public const string sql_update_db_for_v30_11 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_FuelOperType] FOREIGN KEY ([FuelOperType]) REFERENCES [DictionaryFuelOperTypes]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_12 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_Fuel] FOREIGN KEY ([Fuel]) REFERENCES [DictionaryCategories]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_13 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_FuelSupplier] FOREIGN KEY ([FuelSupplier]) REFERENCES [DictionaryPartners]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_14 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_FuelType] FOREIGN KEY ([FuelType]) REFERENCES [DictionaryFuelType]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_15 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_GasStations] FOREIGN KEY ([GasStation]) REFERENCES [DictionaryGasStations]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_16 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_Machine] FOREIGN KEY ([Machine]) REFERENCES [DictionaryMachines]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v30_17 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_ProjectRefueling] FOREIGN KEY ([Project]) REFERENCES [DictionaryProject]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        //public const string sql_update_db_for_v30_18 = @"INSERT INTO [VersionDB] ([Version], [Data]) VALUES (30,GetDate());";

        //Обновление БД до версии 35:
        public const string sql_update_db_for_v35_1 = @"INSERT INTO [DictionaryOperTypes] ([Id], [Name]) VALUES (5, 'Закупка на склад');";
        public const string sql_update_db_for_v35_2 = @"INSERT INTO [DictionaryOperTypes] ([Id], [Name]) VALUES (6, 'Продажа со склада');";
        public const string sql_update_db_for_v35_3 = @"ALTER TABLE [Refueling] ADD CONSTRAINT [FK_FuelDriver] FOREIGN KEY ([Driver]) REFERENCES [DictionaryDrivers]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;";
        public const string sql_update_db_for_v35_4 = @"ALTER TABLE [DictionaryCategories] ADD [Fuel2] int DEFAULT 0 NULL;";
        public const string sql_update_db_for_v35_5 = @"INSERT INTO [VersionDB] ([Version], [Data]) VALUES (35,GetDate());";    
    }
}
